MySQL之INSERT INTO…ON DUPLICATE KEY UPDATE详解

在日常的开发中，经常会遇到如下的场景:查看某条记录是否存在，不存在的话创建一条心记录，存在的话更新某些字段。一般的处理方式如下：

这样的写法可能有如下几点缺陷：

不过，MySql考虑到了这点，提供了insert into … on duplicate key update的语法，该语法在insert的时候，如果insert的数据会引起唯一索引（包括主键索引）的冲突，即这个唯一值重复了，则不会执行insert操作，而执行后面的update操作。

例如，现在有表test，test表中有字段a，在a上有主键或者唯一索引，并且表中只有一条a=1, b=1的数据，现在执行如下的sql：

因为a=1的记录已存在了，所以不会执行insert，而会在该条记录上执行update语言b=b+1,记录会变成a=1,b=2

a=2的记录不存在，所以执行insert 这样我们就无需在应用程序里面再去判断记录是否存在了，也无需关系高并发下数据出错的情况了。

如果行作为新记录被插入，则受影响的行为1；如果原有记录被更新，则受影响行为2；如果原有记录已存在，但是更新的值和原有值相同，则受影响行为0。

多唯一索引冲突 为了测试方便，我们建了下面的数据表：

为了测试两个唯一索引都冲突的情况，然后插入下面的数据：

然后执行：

因为a和b都是唯一索引，插入的数据在两条记录上产生了冲突，然而执行后只有第一条记录被修改：

上面的语句等同于：

如果a=1 or b =2匹配多条记录，只有第一条记录被更新。所以，一般情况下，我们应该避免在有多个唯一索引的表中使用on duplicate key update。

使用values()方法 在update中可以使用values()方法引用在insert中的值，如：

该语句会使a=1的记录中c字段的值更新为6，因为values(c)的值是引用的insert部分的值，在这个例子中就是insert into test values(1,3,5) 中的5，所以最终更新的值为6。

last_insert_id() 如果表含有auto_increment字段，使用insert … on duplicate key update插入或更新后，last_insert_id()返回auto_increment字段的值。

并发控制 在使用例如MyISAM这样的表级锁的分区表上使用insert … on duplicate key update时，会锁住所有分区表，而在例如使用InnoDB这样的行级锁的分区表上则不会锁住所有分区表。

delayed选项 delayed选项会被忽略，当我们使用on duplicate key update时。

根据官方文档：

For INSERT … ON DUPLICATE KEY UPDATE statements, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS flag, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

总结下，insert 1，update 2，update 的值和原来的值一样 0。但如果通过 JDBC 调用，最后一种情况也会返回1，这是因为客户端连接时如果设置了 CLIENT_FOUND_ROWS 标志，会用 rows found 代替 rows affected 当做返回值，而JDBC默认是会设置该标志的。在 JDBC 连接字符串中指定 useAffectedRows=true 可以取消这个flag。

useAffectedRows Don’t set the CLIENT_FOUND_ROWS flag when connecting to the server (not JDBC-compliant, will break most applications that rely on “found” rows vs. “affected rows” for DML statements), but does cause “correct” update counts from “INSERT … ON DUPLICATE KEY UPDATE” statements to be returned by the server. Default: false Since version: 5.1.7

上官方文档：

If a table contains an AUTO_INCREMENT column and INSERT … UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row instead, LAST_INSERT_ID() is not meaningful.

insert：返回刚刚生成的自增ID，update：返回值无意义，MyBatis实测返回0而不是null，因此DAO这么写：

然后调用方试图通过观察 id 是否为null，推测是发生了更新还是插入，这是行不通的。

要获得插入或者更新的ID，可以在SQL执行完后，利用唯一索引再把id查出来：

根据官方文档的描述，INSERT ON DUPLICATE KEY UPDATE 属于 Mixed-mode inserts，只分析SQL无法知道需要几个自增id，在 innodb_autoinc_lock_mode 为 1（默认值）或 2 时，这类 insert 不走 AUTO_INC 表级锁，而是用一个轻量级的 mutex，一次性分配最坏情况下所需要的自增id，至于用不用的完就不管了。上锁的只是分配id的过程，不会锁整个sql语句，这样一来提高了并发度，但代价是和后续insert分配的自增id之间可能存在空洞。具体到INSERT ON DUPLICATE KEY UPDATE，即使最终执行了 update，自增ID也是会增长的，不过这一般不是问题。

Mysql Innodb存储引擎 insert 死锁分析 官网BUG